package com.bos.data.repositories.impl;

import com.auth0.jwt.internal.org.apache.commons.lang3.text.StrBuilder;
import com.bos.common.PageData;
import com.google.common.collect.Iterables;
import com.google.common.collect.Maps;
import org.hibernate.SQLQuery;
import org.hibernate.jpa.HibernateQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;

/**
 * Created by luojie on 2016/7/28.
 */
public abstract class BaseRepository {

    @PersistenceContext
    protected EntityManager entities;

    protected <T> T getScalar(String sql, Class<T> clazz) {
        return this.getScalar(sql, Maps.newHashMap(), clazz);
    }

    protected <T> T getScalar(String sql, Map params, Class<T> clazz) {
        List list = this.getList(sql, params);
        if (list.size() == 0) {
            return null;
        }

        Map first = (Map) Iterables.get(list, 0);
        if (first.entrySet().size() == 0) {
            return null;
        }

        Map.Entry entry = (Map.Entry) Iterables.get(first.entrySet(), 0);
        return (T) entry.getValue();
    }

    protected List getList(String sqlStr) {
        return this.getList(sqlStr, Maps.newHashMap());
    }

    protected List getList(String sqlStr, Map params) {
        Query query = this.entities.createNativeQuery(sqlStr);
        if (params != null) {
            for (Object key : params.keySet()) {
                query.setParameter((String) key, params.get(key));
            }
        }
        return this.toMap(query);
    }

    protected List getList(String sqlStr, Map params, Class clazz) {
        Query query = this.entities.createNativeQuery(sqlStr,clazz);
        if (params != null) {
            for (Object key : params.keySet()) {
                query.setParameter((String) key, params.get(key));
            }
        }
        return query.getResultList();
    }

    protected int executeUpdate(String sqlStr, Map params) {
        Query query = this.entities.createNativeQuery(sqlStr);
        if (params != null) {
            for (Object key : params.keySet()) {
                query.setParameter((String) key, params.get(key));
            }
        }
        return query.executeUpdate();
    }

    protected List getEntityList(String sqlStr, Map params) {
        Query query = this.entities.createQuery(sqlStr);
        if (params != null) {
            for (Object key : params.keySet()) {
                query.setParameter((String) key, params.get(key));
            }
        }

        return query.getResultList();
    }

    protected int getCount(String sqlStr) {
        return this.getCount(sqlStr, Maps.newHashMap());
    }

    protected int getCount(String sqlStr, Map params) {
        Query query = this.entities.createNativeQuery(sqlStr);
        if (params != null) {
            for (Object key : params.keySet()) {
                query.setParameter((String) key, params.get(key));
            }
        }

        int result = (int) query.getSingleResult();
        return result;
    }

    //分页(无条件参数)
    protected PageData getPage(String sqlStr, int pageSize, int pageIndex) {
        return this.getPage(sqlStr, Maps.newHashMap(), pageSize, pageIndex);
    }

    //分页(有条件参数)
    protected PageData getPage(String sqlStr, Map params, int pageSize, int pageIndex) {
        List data = null;
        int total = 0;

        String totalCount = "totalCount";
        if (params.containsKey(totalCount)) {
            total = Integer.parseInt(params.get(totalCount).toString());
            params.remove(totalCount);
        }

        if (pageSize == 0 && pageIndex == 0) {
            data = this.getList(sqlStr, params);
        } else {
            data = this.getList(String.format("select top %d * from (%s) a where a.id not in(select top %d b.id from (%s) b) ", pageSize, sqlStr, pageSize * (pageIndex - 1), sqlStr), params);
        }

        //优化分页查询
        if (total == 0) { //重新计算中总条数
            total = this.getCount(String.format("SELECT count(*) from (%s) a", sqlStr), params);
        }

        return new PageData(data, total, pageSize, pageIndex);
    }

    //此方法用于分页+排序(无搜索条件)
    protected PageData getPage(String sqlStr, int pageSize, int pageIndex, List sort, boolean desc) {
        return this.getPage(sqlStr, Maps.newHashMap(), pageSize, pageIndex, sort, desc);
    }


    /**
     * 此方法用于分页+排序(有搜索条件)
     *
     * @param sqlStr    原始sql
     * @param params    条件参数
     * @param pageSize  每页数据量
     * @param pageIndex 页码
     * @param sort      把要排序的字段放入list中
     * @param desc      true 表示降序
     * @return
     */
    protected PageData getPage(String sqlStr, Map params, int pageSize, int pageIndex, List<String> sort, boolean desc) {
        List data = null;
        int total = 0;

        String totalCount = "totalCount";
        if (params.containsKey(totalCount)) {
            total = Integer.parseInt(params.get(totalCount).toString());
            params.remove(totalCount);
        }


        //优化分页查询
        if (total == 0) { //重新计算中总条数
            total = this.getCount(String.format("SELECT count(*) from (%s) a", sqlStr), params);
        }

        StrBuilder sb = new StrBuilder();
        if (sort != null && sort.size() != 0 && !sort.get(0).trim().isEmpty()) {
            sb.append(" a.");
            for (int i = 0; i < sort.size(); i++) {
                if (i == sort.size() - 1) {
                    sb.append(sort.get(i));
                } else {
                    sb.append(sort.get(i) + ",");
                }
            }
            if (desc == true) {
                sb.append(" desc ");
            }
            //拼接sql
            if (pageSize == 0 && pageIndex == 0) {
                data = this.getList(String.format("SELECT * FROM (%s) a order by %s", sqlStr, sb.toString()), params);
            } else {
                //SELECT  * FROM  ( SELECT top 20  ROW_NUMBER() OVER ( ORDER BY a.id  ) AS rownum ,* from (select * from sales) a ) AS temp WHERE   temp.rownum>10 ;
                data = this.getList(String.format(" SELECT  * FROM  ( SELECT top %d  ROW_NUMBER() OVER ( ORDER BY %s  ) AS rownum ,* from (%s) a ) AS temp WHERE temp.rownum>%d ", pageSize * pageIndex, sb.toString(), sqlStr, pageSize * (pageIndex - 1)), params);
            }
        } else {
            this.getPage(sqlStr, params, pageSize, pageIndex);
        }


        return new PageData(data, total, pageSize, pageIndex);
    }

    private List toMap(Query query) {
        if (query instanceof HibernateQuery) {
            query.unwrap(HibernateQuery.class)
                    .getHibernateQuery()
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            return query.getResultList();
        }

        throw new IllegalStateException();
    }

    private void addScalar(Query query, String column, Type type) {
        if (query instanceof HibernateQuery) {
            query.unwrap(SQLQuery.class).addScalar(column, type);
        }

        throw new IllegalStateException();
    }
}
